package com.red.wood.module.cdn.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.red.wood.common.domain.Page;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.common.util.IMap;
import com.red.wood.module.cdn.dao.CdnDataDao;

@Repository
public class CdnDataDaoImpl implements CdnDataDao {

	private static final Log log = LogFactory.getLog(CdnDataDaoImpl.class);
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Map<String, Object> getPageData(IMap param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		list = filter(listPageDataStatistics(param));
		count = listCountStatistics(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public List<Map<String, Object>> getDetailInfo(String cdnId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.cdnName,t1.cdnAdd,t1.cdnId,t2.name as linkName,t2.mobile,t1.timstamp");
		sql.append(" from t_cdn_cdndata t1 left join t_cdn_linkofficerinfo t2 on t1.cdnId = t2.cdnId where t1.cdnId ='"+cdnId+"'");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public List<Map<String, Object>> listPageDataStatistics(IMap param){
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String provinceId = CommonUtils.getProvinceId(userProvince);
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {//勾选企业
			sql.append("select province,cdnName,cdnId,subNum,ipNum,customerNum,domainNum,houseNum,nodeNum,contacts,contactsPhone from t_cdn_cdndata_statistics cdn where 1 = 1");
			sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "2")) {//勾选机房:表示机房是本省的，企业不分本省外省，子网和节点由机房反推、IP属于机房下
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,cdn.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,house.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = '"+provinceId+"' group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" where 1 = 1");
		}else if(StringUtils.equals(userLevel, "3")) {//勾选客户
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,customer.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,cdn.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		}else if(StringUtils.equals(userLevel, "4")) {//勾选企业、机房,须同时存在
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,cdn.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,house.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "5")) {//勾选企业、客户
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,customer.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,cdn.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "6")) {//勾选机房、客户
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,customer.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,house.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
			sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		}else if(StringUtils.equals(userLevel, "7")) {//勾选企业、机房,客户
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,customer.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,house.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}
		sql.append(getQueryConf(param));
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	
	public List<Map<String, Object>> listPageData(IMap param){
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		
		if(StringUtils.equals(userLevel, "1")) {//初级权限
			sql = getPrimaryLevelSql(param);
			sql.append(getQueryConf(param));
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}else if(StringUtils.equals(userLevel, "2")) {//中级权限
			sql = getMiddleLevelSql(param);
			sql.append(getQueryConf(param));
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}else {//高级权限
			sql.append("select * from (");
			sql.append(getMiddleLevelSql(param));
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getPrimaryLevelSql(param));
			//sql.append(" and exists (select t1.cdnId from t_cdn_cdnNet_node_house t1 where t1.cdnId = t.cdnId and t1.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
			sql.append(" and cdn.province !='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}

	/**
	 * 初级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getPrimaryLevelSql(IMap param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		StringBuffer sql = new StringBuffer();
		sql.append("select cdn.cdnName,cdn.cdnId,sub.subNum,ip.ipNum,customer.customerNum,domain.domainNum,house.houseNum,node.nodeNum,");
		sql.append("       (select link.name from t_cdn_linkofficerinfo link where cdn.cdnId = link.cdnId limit 1) as contacts,");
		sql.append("       (select link.mobile from t_cdn_linkofficerinfo link where cdn.cdnId = link.cdnId limit 1) as contactsPhone");
		sql.append(" from t_cdn_cdndata cdn");
		sql.append(" left join (select count(*) subNum,sub.cdnId from t_cdn_cdnNetInfo sub group by sub.cdnId) sub");
		sql.append(" on cdn.cdnId = sub.cdnId");
		sql.append(" left join (select count(*) ipNum,ip.cdnId from t_cdn_cdnNet_node_house_ipSeg ip where ip.province = '"+userProvince+"' group by ip.cdnId) ip");
		sql.append(" on cdn.cdnId = ip.cdnId");
		sql.append(" left join (select count(*) customerNum,custom.cdnId from t_cdn_customerinfo custom where custom.province = '"+userProvince+"' group by custom.cdnId) customer");
		sql.append(" on cdn.cdnId = customer.cdnId");
		sql.append(" left join (select count(*) domainNum,domain.cdnId from t_cdn_customer_service_domain domain,t_cdn_customerinfo customer where domain.customerId = customer.customerId and domain.cdnId = customer.cdnId and customer.province = '"+userProvince+"' group by domain.cdnId) domain");
		sql.append(" on cdn.cdnId = domain.cdnId");
		sql.append(" left join (select count(*) houseNum,house.cdnId from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId) house");
		sql.append(" on cdn.cdnId = house.cdnId");
		sql.append(" left join (select count(*) nodeNum,node.cdnId from t_cdn_cdnNetInfo_node node group by node.cdnId) node");
		sql.append(" on cdn.cdnId = node.cdnId");
		sql.append(" where 1 = 1 ");
		return sql;
	}
	
	/**
	 * 中级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getMiddleLevelSql(IMap param) {
		StringBuffer sql = new StringBuffer();
		sql.append("select cdn.cdnName,cdn.cdnId,cdn.timstamp,sub.subNum,ip.ipNum,customer.customerNum,domain.domainNum,house.houseNum,node.nodeNum,");
		sql.append("       (select link.name from t_cdn_linkofficerinfo link where cdn.cdnId = link.cdnId limit 1) as contacts,");
		sql.append("       (select link.mobile from t_cdn_linkofficerinfo link where cdn.cdnId = link.cdnId limit 1) as contactsPhone");
		sql.append(" from t_cdn_cdndata cdn");
		sql.append(" left join (select count(1) subNum,t.cdnId from (select sub.cdnId,sub.cdnNetId from t_cdn_cdnNetInfo sub group by sub.cdnId,sub.cdnNetId) t group by t.cdnId) sub");
		sql.append(" on cdn.cdnId = sub.cdnId");
		sql.append(" left join (select count(1) ipNum,t.cdnId from (select ip.cdnId,ip.startIp,ip.endIp from t_cdn_cdnNet_node_house_ipSeg ip group by ip.cdnId,ip.startIp,ip.endIp) t group by t.cdnId) ip");
		sql.append(" on cdn.cdnId = ip.cdnId");
		sql.append(" left join (select count(*) customerNum,custom.cdnId from t_cdn_customerinfo custom group by custom.cdnId) customer");
		sql.append(" on cdn.cdnId = customer.cdnId");
		sql.append(" left join (select count(*) domainNum,domain.cdnId from t_cdn_customer_service_domain domain,t_cdn_customerinfo customer where domain.customerId = customer.customerId and domain.cdnId = customer.cdnId group by domain.cdnId) domain");
		sql.append(" on cdn.cdnId = domain.cdnId");
		sql.append(" left join (select count(1) houseNum,t.cdnId from (select house.cdnId,house.houseId from t_cdn_cdnNet_node_house house group by house.cdnId,house.houseId) t group by t.cdnId) house");
		sql.append(" on cdn.cdnId = house.cdnId");
		sql.append(" left join (select count(1) nodeNum,t.cdnId from (select node.cdnId,nodeId from t_cdn_cdnNetInfo_node node group by node.cdnId,node.nodeId) t group by t.cdnId) node");
		sql.append(" on cdn.cdnId = node.cdnId");
		sql.append(" where 1 = 1 ");
		
		return sql;
	}
	
	public StringBuffer getSqlCount() {
		StringBuffer sql = new StringBuffer();
		sql.append("select cdn.cdnName,cdn.cdnId,cdn.timstamp from t_cdn_cdndata cdn where 1 = 1");
		return sql;
	}
	
	public String listCountStatistics(IMap param) {
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {//勾选企业
			sql.append("select count(1) count from t_cdn_cdndata_statistics cdn where 1 = 1");
			sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "2")) {//勾选机房
			sql.append("select count(1) count ");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" where 1 = 1");
		}else if(StringUtils.equals(userLevel, "3")) {//勾选客户
			sql.append("select count(1) count");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		}else if(StringUtils.equals(userLevel, "4")) {//勾选企业、机房,须同时存在
			sql.append("select count(1) count");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "5")) {//勾选企业、客户
			sql.append("select count(1) count");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "6")) {//勾选机房、客户
			sql.append("select count(1) count");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
			sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		}else if(StringUtils.equals(userLevel, "7")) {//勾选企业、机房,客户
			sql.append("select count(1) count");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" inner join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" inner join (select count(*) customerNum, customer.cdnId");
			sql.append("              from t_cdn_customerinfo customer where customer.province = '"+userProvince+"' group by customer.cdnId) customer");
		    sql.append(" on cdn.cdnId = customer.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public String listCount(IMap param) {
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "3")) {
			sql.append("select count(*) count from (");
			sql.append(getSqlCount());
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSqlCount());
			//sql.append(" and exists (select t1.cdnId from t_cdn_cdnNet_node_house t1 where t1.cdnId = t.cdnId and t1.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
			sql.append(" and cdn.province !='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}else {
			sql.append("select count(*) count from t_cdn_cdndata cdn where 1 = 1");
			sql.append(getQueryConf(param));
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public StringBuffer getQueryConf(IMap param) {
		String cdnName = CommonUtils.valueOf(param.get("cdnName"));
		String cdnId = CommonUtils.valueOf(param.get("cdnId"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer queryConf = new StringBuffer();
		/*if(StringUtils.equals(userLevel, "1") || StringUtils.equals(userLevel, "2")) {
			if(StringUtils.isNotBlank(userProvince)) {
				queryConf.append(" and cdn.province='"+userProvince+"'");
			}else {//如果省份为空，禁止当前用户查看任何信息
				log.warn("======省份为空，请检查当前登录用户=======");
				queryConf.append(" and 1 = 2");
			}
		}*/
		if(StringUtils.isNotBlank(cdnName)) {
			queryConf.append(" and cdn.cdnName like '%"+cdnName+"%'");
		}
		if(StringUtils.isNotBlank(cdnId)) {
			queryConf.append(" and cdn.cdnId like '%"+cdnId+"%'");
		}
		return queryConf;
	}
	
	public List<Map<String, Object>> filter(List<Map<String, Object>> list){
		if(list != null && list.size() > 0) {
			for(Map<String,Object> map : list) {
				map.put("subNum",CommonUtils.filteNull(map.get("subNum")));
				map.put("ipNum",CommonUtils.filteNull(map.get("ipNum")));
				map.put("customerNum",CommonUtils.filteNull(map.get("customerNum")));
				map.put("domainNum",CommonUtils.filteNull(map.get("domainNum")));
				map.put("houseNum",CommonUtils.filteNull(map.get("houseNum")));
				map.put("nodeNum",CommonUtils.filteNull(map.get("nodeNum")));
			}
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> exportData(IMap param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {//初级权限：本省企业本省机房
			sql.append("select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,cdn.customerNum,cdn.domainNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone,house.houseNum");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" left join (select count(1) houseNum,t.cdnId");
			sql.append("              from (select house.cdnId,house.houseId,house.houseProvince from t_cdn_cdnNet_node_house house where house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1) group by house.cdnId,house.houseId) t group by t.cdnId) house");
		    sql.append(" on cdn.cdnId = house.cdnId");
		    sql.append(" where 1 = 1");
		    sql.append(" and cdn.province='"+userProvince+"'");
		}else if(StringUtils.equals(userLevel, "2")){//中级权限：本省企业对应所有资源
			sql.append("select province,cdnName,cdnId,subNum,ipNum,customerNum,domainNum,houseNum,nodeNum,contacts,contactsPhone from t_cdn_cdndata_statistics cdn where 1 = 1");
			sql.append(" and cdn.province='"+userProvince+"'");
		}else {//高级权限：本省企业对应本省所有资源+机房是本省对应外省企业及对应外省企业下所有资源
			sql.append(" select province,cdnName,cdnId,subNum,ipNum,customerNum,domainNum,houseNum,nodeNum,contacts,contactsPhone");
			sql.append(" from t_cdn_cdndata_statistics cdn where 1 = 1");
			sql.append(" and cdn.province = '"+userProvince+"'");
			sql.append(" union all ");
			sql.append(" select cdn.province,cdn.cdnName,cdn.cdnId,cdn.subNum,cdn.ipNum,cdn.customerNum,cdn.domainNum,cdn.houseNum,cdn.nodeNum,cdn.contacts,cdn.contactsPhone");
			sql.append(" from t_cdn_cdndata_statistics cdn");
			sql.append(" where cdn.province !='"+userProvince+"'");
			sql.append(" and exists (select t1.cdnId from t_cdn_cdnNet_node_house t1 where t1.cdnId = cdn.cdnId and t1.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
		}
		sql.append(" limit 10000");
		return jdbcTemplate.queryForList(sql.toString());
	}
}
